package dao;

import entity.Note;
import utils.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author 来苏
 */
public class NoteDao {

    public List<Note> findAll(String sql) throws SQLException {
        //1、创建一个用来放入数据的集合
        List<Note> notes = new ArrayList<>();
        //2、获取连接
        Connection connection = ConnectionUtils.getConnection();

        //4、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5、如果有问号，进行问号的填充
        //6、执行载体，因为是查询，所以返回一个结果集对象-->ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();
        //7、对结果集来进行循环判断
        while (resultSet.next()) {
            //8、如果下一行有数据，则取出这一行里面的每一列到Users对象里面来
            //8.1、创建一个Users对象
            Note note = new Note();
            //8.2、拿出每一列放入到users对象中来
            note.setNoteid(resultSet.getInt(1));
            note.setNotetitle(resultSet.getString(2));
            note.setNotedate(resultSet.getString(3));
            note.setNotereadnum(resultSet.getInt(4));
            note.setNotetext(resultSet.getString(5));
            note.setNotezan(resultSet.getInt(6));
            note.setNotepic(resultSet.getString(7));
            note.setNotetag(resultSet.getString(8));
            //8.3、把Users对象放入到集合中去
            notes.add(note);
        }
        //9、返回该集合
        return notes;
    }

    //查找标签
    public List<String> findTags() throws SQLException {
        //1、创建一个用来放入数据的集合
        List<String> notes = new ArrayList<>();
        //2、获取连接
        Connection connection = ConnectionUtils.getConnection();

        //4、通过连接构建一个载体，这个载体就是用来加载这个命令的
        String sql = "SELECT DISTINCT(notetag) from note";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //6、执行载体，因为是查询，所以返回一个结果集对象-->ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();
        //7、对结果集来进行循环判断
        while (resultSet.next()) {
            notes.add(resultSet.getString(1));
        }
        //9、返回该集合
        return notes;
    }

    public List<Note> findAll() throws SQLException {
        //1、创建一个用来放入数据的集合
        List<Note> notes = new ArrayList<>();
        //2、获取连接
        Connection connection = ConnectionUtils.getConnection();
        //3、书写你要发送给仓库的命令
        String sql = "select noteid,notetitle,notedate,notereadnum,notetext,notezan,notepic,notetag from note";
        //4、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5、如果有问号，进行问号的填充
        //6、执行载体，因为是查询，所以返回一个结果集对象-->ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();
        //7、对结果集来进行循环判断
        while (resultSet.next()) {
            //8、如果下一行有数据，则取出这一行里面的每一列到Users对象里面来
            //8.1、创建一个Users对象
            Note note = new Note();
            //8.2、拿出每一列放入到users对象中来
            note.setNoteid(resultSet.getInt(1));
            note.setNotetitle(resultSet.getString(2));
            note.setNotedate(resultSet.getString(3));
            note.setNotereadnum(resultSet.getInt(4));
            note.setNotetext(resultSet.getString(5));
            note.setNotezan(resultSet.getInt(6));
            note.setNotepic(resultSet.getString(7));
            note.setNotetag(resultSet.getString(8));
            //8.3、把Users对象放入到集合中去
            notes.add(note);
        }
        //9、返回该集合
        return notes;
    }
    public Note selectNote(int noteid) throws SQLException {
        Note note=new Note();
        Connection connection = ConnectionUtils.getConnection();
        //3、书写你要发送给仓库的命令
        String sql = "select noteid,notetitle,notedate,notereadnum,notetext,notezan,notepic,notetag from note where noteid=?";
        //4、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5、如果有问号，进行问号的填充
        preparedStatement.setInt(1,noteid);
        //6、执行载体，因为是查询，所以返回一个结果集对象-->ResultSet
        ResultSet resultSet = preparedStatement.executeQuery();
        //7、对结果集来进行循环判断
        while (resultSet.next()) {
            //8、如果下一行有数据，则取出这一行里面的每一列到Users对象里面来
            //8.1、创建一个Users对象

            //8.2、拿出每一列放入到users对象中来
            note.setNoteid(resultSet.getInt(1));
            note.setNotetitle(resultSet.getString(2));
            note.setNotedate(resultSet.getString(3));
            note.setNotereadnum(resultSet.getInt(4));
            note.setNotetext(resultSet.getString(5));
            note.setNotezan(resultSet.getInt(6));
            note.setNotepic(resultSet.getString(7));
            note.setNotetag(resultSet.getString(8));
        }
        //9、返回note
        return note;
    }

    public void updateNotereadnum(int noteid) throws SQLException {
        Connection connection = ConnectionUtils.getConnection();
        //3、书写你要发送给仓库的命令
        String sql = "update note set notereadnum=notereadnum+1  where noteid=?";
        //4、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5、如果有问号，进行问号的填充
        preparedStatement.setInt(1,noteid);
        preparedStatement.execute();
    }
    public void updateNotezanAdd(int noteid) throws SQLException {
        Connection connection = ConnectionUtils.getConnection();
        //3、书写你要发送给仓库的命令
        String sql = "update note set notezan=notezan+1  where noteid=?";
        //4、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5、如果有问号，进行问号的填充
        preparedStatement.setInt(1,noteid);
        preparedStatement.execute();
    }
    public void updateNotezanSub(int noteid) throws SQLException {
        Connection connection = ConnectionUtils.getConnection();
        //3、书写你要发送给仓库的命令
        String sql = "update note set notezan=notezan-1  where noteid=?";
        //4、通过连接构建一个载体，这个载体就是用来加载这个命令的
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //5、如果有问号，进行问号的填充
        preparedStatement.setInt(1,noteid);
        preparedStatement.execute();
    }

}
